﻿using BCCommon;
using BCEntity.Sim.Admin.AdminPermissionMappingPage;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace BCData.Sim.Admin.AdminPermissionMappingPage
{
    public class AdminPermissionMappingPageData : IAdminPermissionMappingPageData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT AdminPermissionMappingPageId,PageName,PagePathCode,PagePathHashCode,PermissionCode,MappingHashCode,ParentPathCode,PathLevel,Icon,PageOrder FROM AdminPermissionMappingPage";
        private const string QUERY_VIEW = @"SELECT A.AdminPermissionMappingPageId,A.PageName,A.PagePathCode,A.PagePathHashCode,A.PermissionCode,A.MappingHashCode,A.ParentPathCode,A.PathLevel,A.Icon,A.PageOrder,
                                                B.DepartmentId,B.AdminId,B.EnabledInsert,B.EnabledUpdate,B.EnabledDelete,B.EnabledSelect,B.StaffOrganizationMappingRecordId
                                            FROM AdminPermissionMappingPage A
                                            INNER JOIN AdminPermissionRecord B ON UPPER(A.PermissionCode)=UPPER(B.PermissionCode) ";


        public AdminPermissionMappingPageData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<AdminPermissionMappingPageEntity> Add(AdminPermissionMappingPageEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("AdminPermissionMappingPage", new string[] { "PageName", "PagePathCode", "PagePathHashCode", "PermissionCode", "MappingHashCode", "ParentPathCode", "PathLevel", "Icon", "PageOrder" });
            sql = string.Format("{0} {1} WHERE AdminPermissionMappingPageId=LAST_INSERT_ID()", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PageName", entity.PageName, DbType.AnsiString, size: 50);
            parameters.Add("?PagePathCode", entity.PagePathCode, DbType.AnsiString, size: 500);
            parameters.Add("?PagePathHashCode", entity.PagePathHashCode, DbType.AnsiString, size: 50);
            parameters.Add("?PermissionCode", entity.PermissionCode, DbType.AnsiString, size: 150);
            parameters.Add("?MappingHashCode", entity.MappingHashCode, DbType.AnsiString, size: 50);
            parameters.Add("?ParentPathCode", entity.ParentPathCode, DbType.AnsiString, size: 500);
            parameters.Add("?PathLevel", entity.PathLevel, DbType.Int32, size: 10);
            parameters.Add("?Icon", entity.Icon, DbType.AnsiString, size: 50);
            parameters.Add("?PageOrder", entity.PageOrder, DbType.Int32, size: 11);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AdminPermissionMappingPageEntity>(sql, parameters,databaseContext.Transaction);
        }

        public void Delete(long partnerPermissionMappingPageId)
        {
            string sql = @"DELETE FROM AdminPermissionMappingPage WHERE AdminPermissionMappingPageId=?AdminPermissionMappingPageId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminPermissionMappingPageId", partnerPermissionMappingPageId, DbType.Int32, size: 10);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public async Task<IEnumerable<StaffRolePermissionPageRecordEntity>> GetList(int? departmentId)
        {
            if (departmentId.HasValue)
            {
                string sql = string.Format(@"{0} WHERE B.DepartmentId=?DepartmentId ORDER BY A.PagePathCode", QUERY_VIEW);
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?DepartmentId", departmentId.Value, DbType.Int64);
                return await this.databaseContext.Slave.QueryAsync<StaffRolePermissionPageRecordEntity>(sql, parameters);
            }
            else
            {
                return await this.databaseContext.Slave.QueryAsync<StaffRolePermissionPageRecordEntity>(string.Format("{0} ORDER BY PagePathCode", QUERY));
            }
        }

        public async Task<(IEnumerable<AdminPermissionMappingPageEntity>, int)> Query(string permissionCode,string parentPathCode, bool fixedParentPathCode,int? pathLevel, int pageIndex = 1, int pageSize = 10)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(permissionCode))
            {
                wheres.Add("PermissionCode LIKE CONCAT('%',CONCAT(?PermissionCode,'%'))");
                parameters.Add("?PermissionCode", permissionCode, DbType.AnsiString, size: 150);
            }
            if (pathLevel.HasValue) {
                wheres.Add("PathLevel=?PathLevel");
                parameters.Add("?PathLevel", pathLevel, DbType.Int32, size: 10);
            }
            string sql;
            if (!string.IsNullOrWhiteSpace(parentPathCode))
            {
                if (fixedParentPathCode)
                {
                    wheres.Add("ParentPathCode=?ParentPathCode");
                    parameters.Add("?ParentPathCode", parentPathCode, DbType.AnsiString, size: 500);
                }
                else
                {
                    wheres.Add("ParentPathCode LIKE CONCAT('%',CONCAT(?ParentPathCode,'%'))");
                    parameters.Add("?ParentPathCode", parentPathCode, DbType.AnsiString, size: 500);
                }

                sql = string.Format("{0} {1} ORDER BY PageOrder ASC,PagePathCode ASC LIMIT {2},{3};SELECT COUNT(1) FROM AdminPermissionMappingPage {1}",
                            QUERY,
                            wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                            (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                            pageSize);
                return await this.databaseContext.Slave.QueryMultipleAsync<AdminPermissionMappingPageEntity, int>(sql, parameters);
            }
           
            sql = string.Format("{0} {1} ORDER BY PagePathCode ASC LIMIT {2},{3};SELECT COUNT(1) FROM AdminPermissionMappingPage {1}",
                        QUERY,
                        wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                        (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                        pageSize);
            return await this.databaseContext.Slave.QueryMultipleAsync<AdminPermissionMappingPageEntity, int>(sql, parameters);
        }

        public async Task<AdminPermissionMappingPageEntity> Update(AdminPermissionMappingPageEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("AdminPermissionMappingPage", new string[] { "PageName", "PagePathCode", "PagePathHashCode", "MappingHashCode", "ParentPathCode", "PathLevel", "Icon" ,"PermissionCode"},
                    new string[] { "AdminPermissionMappingPageId" });
            sql = string.Format("{0} {1} WHERE AdminPermissionMappingPageId=?AdminPermissionMappingPageId", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminPermissionMappingPageId", entity.AdminPermissionMappingPageId, DbType.Int32, size: 10);
            parameters.Add("?PageName", entity.PageName, DbType.AnsiString, size: 50);
            parameters.Add("?PagePathCode", entity.PagePathCode, DbType.AnsiString, size: 500);
            parameters.Add("?PagePathHashCode", entity.PagePathHashCode, DbType.AnsiString, size: 50);
            parameters.Add("?MappingHashCode", entity.MappingHashCode, DbType.AnsiString, size: 50);
            parameters.Add("?ParentPathCode", entity.ParentPathCode, DbType.AnsiString, size: 500);
            parameters.Add("?PathLevel", entity.PathLevel, DbType.Int32, size: 10);
            parameters.Add("?PermissionCode", entity.PermissionCode, DbType.AnsiString, size: 150);
            parameters.Add("?Icon", entity.Icon, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AdminPermissionMappingPageEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<bool> CheckExists(string pagePathCode)
        {
            string sql = @"SELECT 1 FROM AdminPermissionMappingPage WHERE PagePathCode=?PagePathCode LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PagePathCode", pagePathCode, DbType.AnsiString, size: 500);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> CheckPermissionCodeExists(string permissionCode)
        {
            string sql = @"SELECT 1 FROM AdminPermissionMappingPage WHERE PermissionCode=?PermissionCode LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PermissionCode", permissionCode, DbType.AnsiString, size: 150);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<AdminPermissionMappingPageEntity> GetEntity(long partnerPermissionMappingPageId)
        {
            string sql = string.Format("{0} WHERE AdminPermissionMappingPageId=?AdminPermissionMappingPageId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminPermissionMappingPageId", partnerPermissionMappingPageId, DbType.Int32, size: 10);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AdminPermissionMappingPageEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffRolePermissionPageRecordEntity>> GetListByAdminIdAsync(long? adminId)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = @"SELECT A.AdminPermissionMappingPageId,A.PageName,A.PagePathCode,A.PagePathHashCode,A.PermissionCode,
                                A.MappingHashCode,A.ParentPathCode,A.PathLevel,A.Icon,A.PageOrder
                            FROM AdminPermissionMappingPage A
                            ORDER BY A.PagePathCode, A.PathLevel;";
            if (adminId.HasValue)
            {
                sql = string.Format("{0} WHERE B.AdminId=?AdminId AND B.EnabledSelect=1 GROUP BY A.AdminPermissionMappingPageId ORDER BY A.PagePathCode,A.PathLevel;", QUERY_VIEW);
                parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            }
            return await this.databaseContext.Slave.QueryAsync<StaffRolePermissionPageRecordEntity>(sql, parameters);
        }

        public async Task<bool> UpdatePageOrder(IEnumerable<AdminPermissionMappingPageEntity> entities)
        {
            string sql = SqlCommands.GetUpdateSql("AdminPermissionMappingPage", new string[] { "PageOrder" },
                  new string[] { "AdminPermissionMappingPageId" });
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            databaseContext.BeginTransaction();
            int success = 0;
            foreach (var entity in entities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?AdminPermissionMappingPageId", entity.AdminPermissionMappingPageId, DbType.Int32, size: 10);
                parameters.Add("?PageOrder", entity.PageOrder, DbType.Int32, size: 11);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
                success +=  await this.databaseContext.Master.ExecuteAsync(sql, dynamicParameters, databaseContext.Transaction)>0?1:-1;
            }

            bool result = success == entities.Count();
            if (result)
            {
                databaseContext.Commit();
            }
            return result;
        }

        public async Task<AdminPermissionMappingPageEntity> GetLastRootEntity()
        {
            string sql = string.Format("{0} WHERE ParentPathCode IS NULL OR PathLevel=0 ORDER BY PageOrder DESC LIMIT 1",QUERY);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AdminPermissionMappingPageEntity>(sql);
        }

        public async Task<AdminPermissionMappingPageEntity> GetLastOrderEntityByParentPathCode(string parentPathCode)
        {
            string sql = string.Format("{0} WHERE ParentPathCode=?ParentPathCode ORDER BY PageOrder DESC LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ParentPathCode", parentPathCode, DbType.AnsiString, size: 500);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AdminPermissionMappingPageEntity>(sql, parameters);
        }

        public async Task<IEnumerable<AdminPermissionMappingPageEntity>> GetList(string permissionCode)
        {
            string sql = string.Format("{0} WHERE PermissionCode=?PermissionCode", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PermissionCode", permissionCode, DbType.AnsiString, size: 150);
            return await this.databaseContext.Slave.QueryAsync<AdminPermissionMappingPageEntity>(sql, parameters);
        }
    }
}
